﻿Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data
Imports System.Collections
Imports System.Windows.Forms
Imports DataObject
Imports System.Data.SqlClient

Public Class SqlServerNhanVien

    Private oqLBase As New hDBase
    Private oDataSet As New DataSet
    Private oDatatable As New DataTable

    Public Function GetAllNhanVien(ByVal sConnString As String) As DataTable
        oqLBase.Connection(sConnString)
        oDataSet = oqLBase.GetData("Get_All_NhanViens", "NhanVien")
        Return oDataSet.Tables("NhanVien")
    End Function

    Public Function InsertNhanVien(ByVal sConnString As String, ByVal oNhanVien As NhanVien) As Boolean
        Dim SqlParam As SqlParameter() = {New SqlParameter("@SoNV", SqlDbType.Int), _
                                            New SqlParameter("@MaNV", SqlDbType.VarChar, 20), _
                                            New SqlParameter("@TenNV", SqlDbType.NVarChar, 50), _
                                            New SqlParameter("@GioiTinh", SqlDbType.Bit), _
                                            New SqlParameter("@NgaySinh", SqlDbType.DateTime), _
                                            New SqlParameter("@SoCMTND", SqlDbType.VarChar, 20), _
                                            New SqlParameter("@NgayCap", SqlDbType.DateTime), _
                                            New SqlParameter("@NoiCap", SqlDbType.NVarChar, 50), _
                                            New SqlParameter("@TKNganHang", SqlDbType.VarChar, 20), _
                                            New SqlParameter("@DiaChiNganHang", SqlDbType.NVarChar, 50), _
                                            New SqlParameter("@DiaChiGiaDinh", SqlDbType.NVarChar, 50), _
                                            New SqlParameter("@DTCoQuan", SqlDbType.VarChar, 20), _
                                            New SqlParameter("@DTNhaRieng", SqlDbType.VarChar, 20), _
                                            New SqlParameter("@DTDiDong", SqlDbType.VarChar, 20), _
                                            New SqlParameter("@Email", SqlDbType.VarChar, 30), _
                                            New SqlParameter("@SoViTriCV", SqlDbType.Int)}
        SqlParam(0).Direction = ParameterDirection.Output
        SqlParam(1).Value = oNhanVien.MaNV
        SqlParam(2).Value = oNhanVien.TenNV
        SqlParam(3).Value = oNhanVien.GioiTinh

        If (oNhanVien.NgaySinh.Year > 1) Then
            SqlParam(4).Value = oNhanVien.NgaySinh
        End If

        SqlParam(5).Value = oNhanVien.SoCMTND

        If (oNhanVien.NgayCap.Year > 1) Then
            SqlParam(6).Value = oNhanVien.NgayCap
        End If

        SqlParam(7).Value = oNhanVien.NoiCap
        SqlParam(8).Value = oNhanVien.TKNganHang
        SqlParam(9).Value = oNhanVien.DiaChiNganHang
        SqlParam(10).Value = oNhanVien.DiaChiGiaDinh
        SqlParam(11).Value = oNhanVien.DTCoQuan
        SqlParam(12).Value = oNhanVien.DTNhaRieng
        SqlParam(13).Value = oNhanVien.DTDiDong
        SqlParam(14).Value = oNhanVien.Email
        SqlParam(15).Value = oNhanVien.SoViTriCV
        Try
            oqLBase.Connection(sConnString)
            oqLBase.RunStoreProcedures("Insert_NhanVien", "NhanVien", SqlParam)
            Return True
        Catch ex As Exception
            MessageBox.Show(ex.ToString, "Thông báo lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return False
        End Try
    End Function

    Public Function UpdateNhanVien(ByVal sConnString As String, ByVal oNhanVien As NhanVien) As Boolean
        Dim SqlParam As SqlParameter() = {New SqlParameter("@SoNV", SqlDbType.Int), _
                                            New SqlParameter("@MaNV", SqlDbType.VarChar, 20), _
                                            New SqlParameter("@TenNV", SqlDbType.NVarChar, 50), _
                                            New SqlParameter("@GioiTinh", SqlDbType.Bit), _
                                            New SqlParameter("@NgaySinh", SqlDbType.DateTime), _
                                            New SqlParameter("@SoCMTND", SqlDbType.VarChar, 20), _
                                            New SqlParameter("@NgayCap", SqlDbType.DateTime), _
                                            New SqlParameter("@NoiCap", SqlDbType.NVarChar, 50), _
                                            New SqlParameter("@TKNganHang", SqlDbType.VarChar, 20), _
                                            New SqlParameter("@DiaChiNganHang", SqlDbType.NVarChar, 50), _
                                            New SqlParameter("@DiaChiGiaDinh", SqlDbType.NVarChar, 50), _
                                            New SqlParameter("@DTCoQuan", SqlDbType.VarChar, 20), _
                                            New SqlParameter("@DTNhaRieng", SqlDbType.VarChar, 20), _
                                            New SqlParameter("@DTDiDong", SqlDbType.VarChar, 20), _
                                            New SqlParameter("@Email", SqlDbType.VarChar, 30), _
                                            New SqlParameter("@SoViTriCV", SqlDbType.Int)}
        SqlParam(0).Value = oNhanVien.SoNV
        SqlParam(1).Value = oNhanVien.MaNV
        SqlParam(2).Value = oNhanVien.TenNV
        SqlParam(3).Value = oNhanVien.GioiTinh

        If (oNhanVien.NgaySinh.Year = 1) Then
            SqlParam(4).Value = Nothing
        Else
            SqlParam(4).Value = oNhanVien.NgaySinh
        End If

        SqlParam(5).Value = oNhanVien.SoCMTND

        If (oNhanVien.NgayCap.Year = 1) Then
            SqlParam(6).Value = Nothing
        Else
            SqlParam(6).Value = oNhanVien.NgayCap
        End If

        SqlParam(7).Value = oNhanVien.NoiCap
        SqlParam(8).Value = oNhanVien.TKNganHang
        SqlParam(9).Value = oNhanVien.DiaChiNganHang
        SqlParam(10).Value = oNhanVien.DiaChiGiaDinh
        SqlParam(11).Value = oNhanVien.DTCoQuan
        SqlParam(12).Value = oNhanVien.DTNhaRieng
        SqlParam(13).Value = oNhanVien.DTDiDong
        SqlParam(14).Value = oNhanVien.Email
        SqlParam(15).Value = oNhanVien.SoViTriCV
        Try
            oqLBase.Connection(sConnString)
            oqLBase.RunStoreProcedures("Update_NhanVien", "NhanVien", SqlParam)
            Return True
        Catch ex As Exception
            MessageBox.Show(ex.ToString, "Thông báo lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return False
        End Try
    End Function

    Public Function DeleteNhanVien(ByVal sConnString As String, ByVal SoNhanVien As Integer) As Boolean
        Dim SqlParam As SqlParameter() = {New SqlParameter("@SoNV", SqlDbType.Int)}
        SqlParam(0).Value = SoNhanVien
        Try
            oqLBase.Connection(sConnString)
            oqLBase.RunStoreProcedures("Delete_NhanVien", "NhanVien", SqlParam)
            Return True
        Catch ex As SqlException
            Select Case ex.ErrorCode
                Case -2146232060
                    MessageBox.Show("Không thể xóa dữ liệu này. Nhân viên này đang được sử dụng trong phần nghiệp vụ khác", "Thông báo lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Return False
            End Select
        End Try
    End Function

End Class